package daoImp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import twitter4j.Tweet;
import twitter4j.internal.logging.Logger;
import util.DateUtil;
import dao.TweetsDAO;

public class TweetsDAOImp implements TweetsDAO{

	private static TweetsDAOImp uniqueInstance = new TweetsDAOImp();
	private ConnectionPool pool = ConnectionPool.getInstance();
	private Logger logger;
	
	public static TweetsDAO getInstance() {
		return uniqueInstance;
	}

	private TweetsDAOImp() {
		logger = Logger.getLogger(TweetsDAOImp.class);
	}

	public void saveTweets(List<Tweet> tweets, String trend, Date date) {
		String sql = null;
		Statement stmt = null;
		Connection connection;
		try {
			connection = pool.getConnection();
		} catch (Exception e) {
			logger.error("connection error : ", e);
			return;
		}
		try {
			connection.setAutoCommit(false);
			stmt = connection.createStatement();
		} catch (SQLException e1) {
			logger.error("connection error : ", e1);
		}
		String dateStr = DateUtil.getNewsDateStr(date);
		trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
		for (Tweet tweet : tweets) {
			String text = tweet.getText().replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "insert into tweets (tweet_id, trend, text, date)" + "select " + tweet.getId() + ",'" + trend + "','"+ text + "','" +dateStr + "'" +"from dual where not exists ( select * from tweets "+ 
					"where tweets.tweet_id = " +  tweet.getId() + ");";
			try {
				stmt.executeUpdate(sql);
			} catch (SQLException e) {
				logger.error("sql error : " + sql, e);
			}
		}
		try {
			connection.commit();
		} catch (SQLException e) {
			logger.error("sql error : " + sql, e);
		}finally{
			pool.returnConnection(connection);
		}
	}

	public ArrayList<Long> getTweetsIDsByTrend(String trend, Date date) {
		String sql = null;
		ArrayList<Long> tweetIDs = new ArrayList<Long>();
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				logger.error("connection error : ", e);
				return tweetIDs;
			}
			Statement stmt = connection.createStatement();
			String dateStr = DateUtil.getNewsDateStr(date);
			trend = trend.replaceAll("'", "\\\\'").replaceAll("\\)", "\\\\)").replaceAll("\\(", "\\\\(");
			sql = "select tweet_id from tweets where trend = '" + trend + "' and date = '" + dateStr +"';";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				long tweetid = rs.getLong(1);
				tweetIDs.add(tweetid);
			}
			pool.returnConnection(connection);
		} catch (SQLException e) {
			logger.error("connection error : ", e);
		}
		return tweetIDs;
	}

	public ArrayList<String> getTweetsText() {
		String sql = null;
		ArrayList<String> tweets = new ArrayList<String>();
		try {
			Connection connection;
			try {
				connection = pool.getConnection();
			} catch (Exception e) {
				logger.error("connection error : ", e);
				return tweets;
			}
			Statement stmt = connection.createStatement();
			sql = "select text from tweets;";
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String text = rs.getString(1);
				tweets.add(text);
			}
			pool.returnConnection(connection);
		} catch (SQLException e) {
			logger.error("connection error : ", e);
		}
		return tweets;
	}
}
